const connection = require('../app/database')
const { APP_HOST, APP_PORT } = require('../app/config')

const sqlFragment = `
  SELECT 
    m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
    JSON_OBJECT('id', u.id, 'name', u.name,'avatarUrl', u.avatar_url) author,
    IF(COUNT(l.id),JSON_ARRAYAGG(
        JSON_OBJECT('id', l.id, 'name', l.name)
      ),NULL) labels,
    (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
    JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt,
                'user', JSON_OBJECT('id', cu.id, 'name', cu.name,'avatarUrl', cu.avatar_url))
    ),NULL) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id WHERE m.id = c.moment_id) comments,
    (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount,
    (SELECT JSON_ARRAYAGG(CONCAT('${APP_HOST}:${APP_PORT}/moment/picture/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images,
    (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
  FROM moment m
  LEFT JOIN user u ON m.user_id = u.id
  LEFT JOIN moment_label ml ON m.id = ml.moment_id
  LEFT JOIN label l ON ml.label_id = l.id
`

class MomentService {
    async createMoment(content, userId) {
        console.log(userId);
        const statement = `INSERT INTO moment (content, user_id) VALUES (?, ?);`;
        const [result] = await connection.execute(statement, [content, userId])
        return result
    }
    async getMomentList(offset, size) {
        const statement = `
            ${sqlFragment}
            GROUP BY m.id 
            LIMIT ?, ?;  
        `;
        const [result] = await connection.execute(statement, [offset, size]);
        return result;
    }
    async getMomentById(id) {
        const statement = `
        ${sqlFragment}
        WHERE m.id = ?
        GROUP BY m.id;  
    `;
        const [result] = await connection.execute(statement, [id]);
        return result;
    }
    async updateMoment(momentId, content) {
        const statement = `UPDATE moment SET content = ? WHERE id = ?;`
        const [result] = await connection.execute(statement, [content, momentId])
        return result
    }
    async removeMoment(momentId) {
        const statement = `DELETE FROM moment WHERE id = ?;`
        const [result] = await connection.execute(statement, [momentId])
        return result
    }
    async addLabel(momentId, labelId) {
        const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`;
        const [result] = await connection.execute(statement, [momentId, labelId]);
        return result;
    }

}
module.exports = new MomentService()